{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 表的横向拼接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 连接表的类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**一对一**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩\n",
       "0   1  小张  100  650\n",
       "1   2  小王  101  600\n",
       "2   3  小李  102  578\n",
       "3   4  小赵  103  550"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =0)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    学号  班级\n",
       "0  100  一班\n",
       "1  101  一班\n",
       "2  102  二班\n",
       "3  103  三班"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =1)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  一班\n",
       "2   3  小李  102  578  二班\n",
       "3   4  小赵  103  550  三班"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#一对一连接\n",
    "pd.merge(df1,df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩\n",
       "0  小张  100   650\n",
       "1  小王  101   600\n",
       "2  小李  102   578"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =2)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**多对一**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩  e_成绩\n",
       "0  小张  100   650   586\n",
       "1  小张  100   650   602\n",
       "2  小王  101   600   691\n",
       "3  小王  101   600   702\n",
       "4  小李  102   578   645\n",
       "5  小李  102   578   676"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =2)\n",
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =3)\n",
    "pd.merge(df1,df2,on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**多对多**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩  e_成绩\n",
       "0  小张  100   650   586\n",
       "1  小张  100   650   602\n",
       "2  小张  100   610   586\n",
       "3  小张  100   610   602\n",
       "4  小王  101   600   691\n",
       "5  小王  101   600   702\n",
       "6  小李  102   578   645\n",
       "7  小李  102   578   676\n",
       "8  小李  102   542   645\n",
       "9  小李  102   542   676"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =4)\n",
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =3)\n",
    "pd.merge(df1,df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 连接键的类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**默认以公共列作为连接键**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  一班\n",
       "2   3  小李  102  578  二班\n",
       "3   4  小赵  103  550  三班"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =0)\n",
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =1)\n",
    "df1\n",
    "df2\n",
    "pd.merge(df1,df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**用on来指定连接键**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  一班\n",
       "2   3  小李  102  578  二班\n",
       "3   4  小赵  103  550  三班"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  一班\n",
       "2   3  小李  102  578  二班\n",
       "3   4  小赵  103  550  三班"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#连接键有多个\n",
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =0)\n",
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =5)\n",
    "pd.merge(df1,df2,on=[\"姓名\",\"学号\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**分别指定左右连接键**  \n",
    "当两个表面有公共列时，实际值一样，但列名不同。用left_on和right_on来指明连接的列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>编号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>101</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>102</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>103</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   编号   成绩   学号  班级\n",
       "0   1  小张  100  650  100  一班\n",
       "1   2  小王  101  600  101  一班\n",
       "2   3  小李  102  578  102  二班\n",
       "3   4  小赵  103  550  103  三班"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =6)\n",
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =1)\n",
    "pd.merge(df1,df2,left_on = \"编号\",right_on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**把索引列当作连接键**  \n",
    "索引列不算真正的列，当公共列是索引列时，就要把索引列单做连接键，使用left_index、right_index来设置索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     名次  姓名   成绩\n",
       "编号              \n",
       "100   1  小张  650\n",
       "101   2  小王  600\n",
       "102   3  小李  578\n",
       "103   4  小赵  550"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =7)\n",
    "df1.set_index(\"编号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级\n",
       "学号     \n",
       "100  一班\n",
       "101  一班\n",
       "102  二班\n",
       "103  三班"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =1)\n",
    "df2.set_index(\"学号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>600</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>578</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>550</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     名次  姓名   成绩  班级\n",
       "编号                  \n",
       "100   1  小张  650  一班\n",
       "101   2  小王  600  一班\n",
       "102   3  小李  578  二班\n",
       "103   4  小赵  550  三班"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#当左右表的连接键均为索引\n",
    "pd.merge(df1.set_index(\"编号\"),df2.set_index(\"学号\"),left_index = True,right_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    学号  班级\n",
       "0  100  一班\n",
       "1  101  一班\n",
       "2  102  二班\n",
       "3  103  三班"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/chapter11.xlsx\",sheet_name =1)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>成绩</th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>650</td>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>600</td>\n",
       "      <td>101</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>578</td>\n",
       "      <td>102</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>550</td>\n",
       "      <td>103</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   成绩   学号  班级\n",
       "0   1  小张  650  100  一班\n",
       "1   2  小王  600  101  一班\n",
       "2   3  小李  578  102  二班\n",
       "3   4  小赵  550  103  三班"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#索引列和普通索引列混用\n",
    "pd.merge(df1.set_index(\"编号\"),df2,left_index = True,right_on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 连接方式  \n",
    "用参数how来指明具体的连接方式,如果不指定默认是内连接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**内连接(inner)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩\n",
       "0   1  小张  100  650\n",
       "1   2  小王  101  600\n",
       "2   3  小李  102  578\n",
       "3   4  小赵  103  550"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =0)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小钱</td>\n",
       "      <td>104</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  班级\n",
       "0  小张  100  一班\n",
       "1  小王  101  一班\n",
       "2  小李  102  二班\n",
       "3  小钱  104  三班"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =8)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**左连接**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y   班级\n",
       "0   1   小张  100  650   小张   一班\n",
       "1   2   小王  101  600   小王   一班\n",
       "2   3   小李  102  578   小李   二班\n",
       "3   4   小赵  103  550  NaN  NaN"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"left\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**右连接**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>小钱</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    名次 姓名_x   学号     成绩 姓名_y  班级\n",
       "0  1.0   小张  100  650.0   小张  一班\n",
       "1  2.0   小王  101  600.0   小王  一班\n",
       "2  3.0   小李  102  578.0   小李  二班\n",
       "3  NaN  NaN  104    NaN   小钱  三班"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**外连接**  \n",
    "就是两个表的并集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩  e_成绩\n",
       "0  小张  100   650   586\n",
       "1  小张  100   650   602\n",
       "2  小王  101   600   691\n",
       "3  小王  101   600   702\n",
       "4  小李  102   578   645\n",
       "5  小李  102   578   676"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**重复列名处理**  \n",
    "pd.merge()方法默认会给这些重复的列名加后缀\\_x,\\_y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**自定义重复列名**  \n",
    "用suffixes参数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_L</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_R</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_L   学号   成绩 姓名_R  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2,on=\"学号\",how=\"inner\",suffixes=[\"_L\",\"_R\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 表的纵向拼接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**普通合并**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号   姓名  班级\n",
       "0   1   许丹  一班\n",
       "1   2  李旭文  一班\n",
       "2   3   程成  一班\n",
       "3   4   赵涛  一班"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =9)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号  姓名  班级\n",
       "0   1  赵义  二班\n",
       "1   2  李鹏  二班\n",
       "2   3  李伟  二班\n",
       "3   4  吴迪  二班"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =10)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号   姓名  班级\n",
       "0   1   许丹  一班\n",
       "1   2  李旭文  一班\n",
       "2   3   程成  一班\n",
       "3   4   赵涛  一班\n",
       "0   1   赵义  二班\n",
       "1   2   李鹏  二班\n",
       "2   3   李伟  二班\n",
       "3   4   吴迪  二班"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**索引设置**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  班级\n",
       "0   许丹  一班\n",
       "1  李旭文  一班\n",
       "2   程成  一班\n",
       "3   赵涛  一班\n",
       "4   赵义  二班\n",
       "5   李鹏  二班\n",
       "6   李伟  二班\n",
       "7   吴迪  二班"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1.set_index(\"编号\"),df2.set_index(\"编号\")],ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**重叠数据合并**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号   姓名  班级\n",
       "0   1   许丹  一班\n",
       "1   2  李旭文  一班\n",
       "2   3   程成  一班\n",
       "3   4   赵涛  一班\n",
       "4   5   吴迪  二班"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =11)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号  姓名  班级\n",
       "0   1  赵义  二班\n",
       "1   2  李鹏  二班\n",
       "2   3  李伟  二班\n",
       "3   4  吴迪  二班"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel(r\"../Data/Chapter11.xlsx\",sheet_name =10)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  班级\n",
       "0   许丹  一班\n",
       "1  李旭文  一班\n",
       "2   程成  一班\n",
       "3   赵涛  一班\n",
       "4   吴迪  二班\n",
       "5   赵义  二班\n",
       "6   李鹏  二班\n",
       "7   李伟  二班\n",
       "8   吴迪  二班"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1.set_index(\"编号\"),df2.set_index(\"编号\")],ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>吴迪</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>李伟</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  班级\n",
       "0   许丹  一班\n",
       "1  李旭文  一班\n",
       "2   程成  一班\n",
       "3   赵涛  一班\n",
       "4   吴迪  二班\n",
       "5   赵义  二班\n",
       "6   李鹏  二班\n",
       "7   李伟  二班"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#删除重复值\n",
    "pd.concat([df1.set_index(\"编号\"),df2.set_index(\"编号\")],ignore_index = True).drop_duplicates()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "第11章 多表拼接",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
